package com.sh.data.engine.domain.shims.db;

import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.PreviewDataDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import com.sh.data.engine.domain.shims.db.model.TableSizeDomain;
import com.sh.data.engine.domain.shims.sql.builder.DDLBuilder;
import com.sh.data.engine.domain.shims.sql.builder.QueryBuilder;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;

import java.net.ConnectException;
import java.net.SocketTimeoutException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Locale;
import java.util.Objects;

@Getter
@Slf4j
public abstract class BaseDbManager {

    protected DbOptions dbOptions;

    protected static final int DEFAULT_FETCH_SIZE = 1000;

    public BaseDbManager(DbOptions options) {
        this.dbOptions = options;
        initOptionDefaults();
    }

    protected void initOptionDefaults() {
        if (null != dbOptions && dbOptions.getFetchSize() == null) {
            log.debug("Using default fetchSize of " + DEFAULT_FETCH_SIZE);
            dbOptions.setFetchSize(DEFAULT_FETCH_SIZE);
        }
    }

    public DbOptions getDbOptions() {
        return this.dbOptions;
    }

    public String getSchema() {
        return this.dbOptions.getSchema();
    }

    /**
     * 获取数据库连接,连接由使用方自己关闭，切记！！！
     *
     * @return
     * @throws SQLException
     */
    public abstract Connection getConnection() throws SQLException;

    /**
     * 测试连通性
     *
     * @throws SQLException
     */
    public abstract void testConnection() throws SQLException;

    /**
     * 获取数据库版本号
     *
     * @return
     */
    public abstract String getDbVersion() throws SQLException;

    /**
     * 数据库是否支持schema
     *
     * @return
     */
    public abstract boolean hasSchema();

    /**
     * 获取所有schema
     *
     * @return
     * @throws SQLException
     */
    public abstract List<String> getSchemaList() throws SQLException;

    /**
     * 获取指定dbName下的表列表，若dbName下有多个schema，则会获取所有schema的表
     *
     * @return
     * @throws SQLException
     */
    public abstract List<TableInfoDomain> getTableList(String dbName) throws SQLException;

    /**
     * 获取指定dbName及schema下的表列表
     *
     * @param dbName
     * @param schema
     * @return
     * @throws SQLException
     */
    public abstract List<TableInfoDomain> getTableList(String dbName, String schema)
        throws SQLException;

    /**
     * 获取指定dbName下的视图列表，若dbName下有多个schema，则会获取所有schema的视图
     *
     * @return
     * @throws SQLException
     */
    public abstract List<TableInfoDomain> getViewList(String dbName) throws SQLException;

    /**
     * 获取指定dbName及schema下的视图列表
     *
     * @param dbName
     * @param schema
     * @return
     * @throws SQLException
     */
    public abstract List<TableInfoDomain> getViewList(String dbName, String schema)
        throws SQLException;

    /**
     * 获取指定dbName下的表和视图列表，若dbName下有多个schema，则会获取所有schema的表和视图
     *
     * @return
     * @throws SQLException
     */
    public abstract List<TableInfoDomain> getTableAndViewList(String dbName) throws SQLException;

    /**
     * 获取指定dbName及schema下的表和视图列表
     *
     * @param dbName
     * @param schema
     * @return
     * @throws SQLException
     */
    public abstract List<TableInfoDomain> getTableAndViewList(String dbName, String schema)
        throws SQLException;

    /**
     * 获取表或视图信息
     *
     * @param dbName
     * @param tableName
     * @return
     * @throws SQLException
     */
    public abstract TableInfoDomain getTableInfoByTableName(String dbName, String tableName)
        throws SQLException;

    /**
     * 获取表或视图信息
     *
     * @param dbName
     * @param schema
     * @param tableName
     * @return
     * @throws SQLException
     */
    public abstract TableInfoDomain getTableInfoByTableName(
        String dbName, String schema, String tableName) throws SQLException;

    /**
     * 获取表或视图的字段信息
     *
     * @param tableName
     * @return
     * @throws SQLException
     */
    public abstract List<FieldInfoDomain> getFieldList(String dbName, String tableName)
        throws SQLException;

    /**
     * 获取表或视图的字段信息
     *
     * @param dbName
     * @param schema
     * @param tableName
     * @return
     * @throws SQLException
     */
    public abstract List<FieldInfoDomain> getFieldList(String dbName, String schema, String tableName)
        throws SQLException;

    public abstract List<FieldInfoDomain> getFieldList(String dbName, String schema, String tableName, Connection connection)
        throws SQLException;

    public abstract List<TableInfoDomain> getFliterTableList(String dbName, String schema,
                                                             String tableSQL) throws SQLException;

    public abstract List<FieldInfoDomain> getFliterFieldList(String tableName,
                                                             String fieldName) throws SQLException;

    /**
     * 获取预览数据
     *
     * @param tableName
     * @return
     * @throws SQLException
     */
    public abstract PreviewDataDomain previewData(String tableName) throws SQLException;

    public abstract List<String> listFieldData(String sql, Connection connection);


    /**
     * 获取预览数据
     *
     * @param schema
     * @param tableName
     * @return
     * @throws SQLException
     */
    public abstract PreviewDataDomain previewData(String schema, String tableName)
        throws SQLException;

    /**
     * 判断表名是否已经存在
     *
     * @param dbName
     * @param tableName
     * @return
     * @throws SQLException
     */
    @Deprecated
    public abstract boolean isTableNameExist(String dbName, String tableName) throws SQLException;

    /**
     * 判断表名是否已经存在
     *
     * @param dbName
     * @param schema
     * @param tableName
     * @return
     * @throws SQLException
     */
    public abstract boolean isTableNameExist(String dbName, String schema, String tableName)
        throws SQLException;

    /**
     * 获取同步过来的表
     *
     * @param dbName
     * @param viewNameLists
     * @return
     * @throws SQLException
     */
    public abstract List<TableInfoDomain> getTableList(String dbName, List<String> viewNameLists)
        throws SQLException;

    /**
     * 获取同步过来的视图
     *
     * @param dbName
     * @param viewNameLists
     * @return
     * @throws SQLException
     */
    public abstract List<TableInfoDomain> getViewList(String dbName, List<String> viewNameLists)
        throws SQLException;

    public abstract List<TableSizeDomain> getTableSizeList(String dbName, String schema) throws SQLException;

    /**
     * 获取QueryBuilder,用完记得调用QueryBuilder的close方法来关闭connection连接
     *
     * @return
     * @throws SQLException
     */
    public abstract QueryBuilder getQueryBuilder() throws SQLException;

    /**
     * 获取DDLBuilder,用完记得调用DDLBuilder的close方法来关闭connection连接
     *
     * @return
     * @throws SQLException
     */
    public abstract DDLBuilder getDDLBuilder() throws SQLException;

    /**
     * 修改表字段描述信息
     *
     * @return
     * @throws SQLException
     */
    public abstract boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException;

    /**
     * 用特殊符号包裹字段名
     *
     * @param colName
     * @return
     */
    public String escapeColName(String colName) {
        final String escape = dbOptions.getDatabase().getEscape();
        return escape + colName + escape;
    }

    /**
     * 用特殊符号包裹字段名
     *
     * @param colNames
     * @return
     */
    public String[] escapeColNames(String... colNames) {
        String[] escaped = new String[colNames.length];
        int i = 0;
        for (String colName : colNames) {
            escaped[i++] = escapeColName(colName);
        }
        return escaped;
    }

    public String escapeTableName(String tableName) {
        final String escape = dbOptions.getDatabase().getEscape();
        return escape + tableName + escape;
    }

    /**
     * 通用的关闭方法
     *
     * @param closeables
     */
    public void close(AutoCloseable... closeables) {
        if (null == closeables || closeables.length == 0) {
            return;
        }

        for (AutoCloseable closeable : closeables) {
            if (null != closeable) {
                try {
                    closeable.close();
                } catch (Exception e) {
                }
            }
        }
    }

    public String toJavaType(int sqlType) {
        if (sqlType == Types.INTEGER) {
            return "Integer";
        } else if (sqlType == Types.VARCHAR) {
            return "String";
        } else if (sqlType == Types.CHAR) {
            return "String";
        } else if (sqlType == Types.LONGVARCHAR) {
            return "String";
        } else if (sqlType == Types.NVARCHAR) {
            return "String";
        } else if (sqlType == Types.NCHAR) {
            return "String";
        } else if (sqlType == Types.LONGNVARCHAR) {
            return "String";
        } else if (sqlType == Types.NUMERIC) {
            return "java.math.BigDecimal";
        } else if (sqlType == Types.DECIMAL) {
            return "java.math.BigDecimal";
        } else if (sqlType == Types.BIT) {
            return "Boolean";
        } else if (sqlType == Types.BOOLEAN) {
            return "Boolean";
        } else if (sqlType == Types.TINYINT) {
            return "Integer";
        } else if (sqlType == Types.SMALLINT) {
            return "Integer";
        } else if (sqlType == Types.BIGINT) {
            return "Long";
        } else if (sqlType == Types.REAL) {
            return "Float";
        } else if (sqlType == Types.FLOAT) {
            return "Double";
        } else if (sqlType == Types.DOUBLE) {
            return "Double";
        } else if (sqlType == Types.DATE) {
            return "java.sql.Date";
        } else if (sqlType == Types.TIME) {
            return "java.sql.Time";
        } else if (sqlType == Types.TIMESTAMP) {
            return "java.sql.Timestamp";
        } else {
            return null;
        }
    }

    public String toDbType(int sqlType, String fieldType, Integer columnSize, Integer digitSize) {
        switch (dbOptions.getDatabase()) {
            case HIVE:
                return toHiveType(sqlType, fieldType);
            case POSTGRESQL:
                return toPostgresqlType(sqlType, fieldType, columnSize, digitSize);
            case MYSQL:
                return toMysqlType(sqlType);
            case ORACLE:
                return toOracleType(sqlType);
            case SAP:
                return toSapType(sqlType, fieldType);
            case DB2:
                return toDb2Type(sqlType);
            case SQLSERVER:
                return toSqlserverType(sqlType);
            case TIDB:
                return toTiDBType(sqlType, fieldType);
            case KINGBASE8:
                return toKingBase8Type(sqlType, fieldType);
            case DM:
                return toDMType(sqlType, fieldType);
            default:
                throw new UnsupportedOperationException("暂不支持");
        }
    }

    public String toDbType(int sqlType, String fieldType) {
        switch (dbOptions.getDatabase()) {
            case HIVE:
                return toHiveType(sqlType, fieldType);
            case POSTGRESQL:
                return toPostgresqlType(sqlType, fieldType);
            case MYSQL:
                return toMysqlType(sqlType);
            case ORACLE:
                return toOracleType(sqlType);
            case SAP:
                return toSapType(sqlType, fieldType);
            case DB2:
                return toDb2Type(sqlType);
            case SQLSERVER:
                return toSqlserverType(sqlType);
            case TIDB:
                return toTiDBType(sqlType, fieldType);
            case KINGBASE8:
                return toKingBase8Type(sqlType, fieldType);
            case DM:
                return toDMType(sqlType, fieldType);
            default:
                throw new UnsupportedOperationException("暂不支持");
        }
    }

    public String toDbType(int sqlType) {
        switch (dbOptions.getDatabase()) {
            case HIVE:
                return toHiveType(sqlType);
            case POSTGRESQL:
                return toPostgresqlType(sqlType);
            case MYSQL:
                return toMysqlType(sqlType);
            case ORACLE:
                return toOracleType(sqlType);
            case SAP:
                return toSapType(sqlType);
            case DB2:
                return toDb2Type(sqlType);
            case SQLSERVER:
                return toSqlserverType(sqlType);
            case TIDB:
                return toTiDBType(sqlType);
            case KINGBASE8:
                return toKingBase8Type(sqlType);
            case DM:
                return toDMType(sqlType);
            default:
                throw new UnsupportedOperationException("暂不支持");
        }
    }

    public String toDMType(int sqlType, String fieldType) {
        if (sqlType == 0) {
            String lowerCase = fieldType.toLowerCase(Locale.ROOT);
            if (lowerCase.startsWith("in") || lowerCase.startsWith("smallint")) {
                return "BIGINT";
            } else if (lowerCase.startsWith("time")) {
                return "TIMESTAMP";
            } else if (lowerCase.startsWith("date")) {
                return "DATE";
            } else if (lowerCase.startsWith("float")
                || lowerCase.startsWith("numeric")
                || lowerCase.startsWith("decimal")
                || lowerCase.startsWith("double")) {
                return "DOUBLE";
            } else if (lowerCase.startsWith("bit") || lowerCase.startsWith("boolean")) {
                return "boolean";
            } else if (lowerCase.startsWith("bigint")) {
                return "BIGINT";
            } else if (lowerCase.startsWith("blob")
                || lowerCase.startsWith("binary")
                || lowerCase.startsWith("varcharbinary")
                || lowerCase.startsWith("longvarcharbinary")) {
                return "blob";
            } else if (lowerCase.startsWith("clob")) {
                return "clob";
            } else {
                return "VARCHAR(400)";
            }
        }
        return toDMType(sqlType);
    }

    public String toDMType(int sqlType) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "BIGINT";
            case Types.VARCHAR:
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "VARCHAR(400)";
            case Types.DATE:
                return "DATE";
            case Types.TIME:
            case Types.TIMESTAMP:
                return "TIMESTAMP";
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                return "DOUBLE";
            case Types.BIT:
            case Types.BOOLEAN:
                return "boolean";
            case Types.TINYINT:
                return "smallint";
            case Types.BIGINT:
                return "BIGINT";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "blob";
            default:
                return "varchar2";
        }
    }

    public String toKingBase8Type(int sqlType, String fieldType) {
        if (sqlType == 0) {
            String toLowerCase = fieldType.toLowerCase(Locale.ROOT);
            // inter int
            if (toLowerCase.startsWith("in") || toLowerCase.startsWith("bigint")) {
                return "int4";
            } else if (toLowerCase.startsWith("time")) {
                return "timestamp(0)";
            } else if (toLowerCase.startsWith("date")) {
                return "date";
            } else if (toLowerCase.startsWith("float")
                || toLowerCase.startsWith("numeric")
                || toLowerCase.startsWith("decimal")
                || toLowerCase.startsWith("double")) {
                return "float8";
            } else if (toLowerCase.startsWith("bit") || toLowerCase.startsWith("boolean")) {
                return "boolean";
            } else if (toLowerCase.startsWith("blob")
                || toLowerCase.startsWith("binary")
                || toLowerCase.startsWith("varcharbinary")
                || toLowerCase.startsWith("longvarcharbinary")) {
                return "bytea";
            } else {
                return "text";
            }
        }
        return toKingBase8Type(sqlType);
    }

    public String toKingBase8Type(int sqlType) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "int4";
            case Types.VARCHAR:
                return "varchar(500)";
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "text";
            case Types.DATE:
                return "date";
            case Types.TIME:
            case Types.TIMESTAMP:
                return "timestamp(0)";
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                return "float8";
            case Types.BIT:
            case Types.BOOLEAN:
                return "boolean";
            case Types.TINYINT:
                return "int2";
            case Types.BIGINT:
                return "int8";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "bytea";
            default:
                return "text";
        }
    }

    public String toHiveType(int sqlType, String fieldType) {
        if (sqlType == 0) {
            String lowerCase = fieldType.toLowerCase(Locale.ROOT);
            if (lowerCase.startsWith("in") || lowerCase.startsWith("smallint")) {
                return "INT";
            } else if (lowerCase.startsWith("time")) {
                return "TIMESTAMP";
            } else if (lowerCase.startsWith("date")) {
                return "DATE";
            } else if (lowerCase.startsWith("float")
                || lowerCase.startsWith("numeric")
                || lowerCase.startsWith("decimal")
                || lowerCase.startsWith("double")) {
                return "DOUBLE";
            } else if (lowerCase.startsWith("bit") || lowerCase.startsWith("boolean")) {
                return "BOOLEAN";
            } else if (lowerCase.startsWith("bigint")) {
                return "BIGINT";
            } else if (lowerCase.startsWith("blob")
                || lowerCase.startsWith("binary")
                || lowerCase.startsWith("varcharbinary")
                || lowerCase.startsWith("longvarcharbinary")) {
                return "BINARY";
            } else {
                return "STRING";
            }
        }
        return toHiveType(sqlType);
    }

    public String toHiveType(int sqlType) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "INT";
            case Types.VARCHAR:
                return "varchar(500)";
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "STRING";
            case Types.DATE:
                return "DATE";
            case Types.TIME:
            case Types.TIMESTAMP:
                return "TIMESTAMP";
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                return "DOUBLE";
            case Types.BIT:
            case Types.BOOLEAN:
                return "BOOLEAN";
            case Types.TINYINT:
                return "TINYINT";
            case Types.BIGINT:
                return "BIGINT";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "BINARY";
            default:
                return "STRING";
        }
    }

    public String toPostgresqlType(
        int sqlType, String fieldType, Integer columnSize, Integer digitSize) {
        if (sqlType == 0) {
            String toLowerCase = fieldType.toLowerCase(Locale.ROOT);
            // inter int
            if (toLowerCase.startsWith("in") || toLowerCase.startsWith("bigint")) {
                return "int4";
            } else if (toLowerCase.equalsIgnoreCase("time")) {
                return "time";
            } else if (toLowerCase.equalsIgnoreCase("date")) {
                return "date";
            } else if (toLowerCase.equalsIgnoreCase("datetime")) {
                return "timestamp(0)";
            } else if (toLowerCase.startsWith("float")) {
                return "float4";
            } else if (toLowerCase.startsWith("double")) {
                return "float8";
            } else if (toLowerCase.startsWith("numeric") || toLowerCase.startsWith("decimal")) {
                if (Objects.nonNull(columnSize)
                    && Objects.nonNull(digitSize)
                    && columnSize > 0
                    && digitSize >= 0) {
                    return "numeric(" + columnSize + "," + digitSize + ")";
                } else {
                    return "numeric";
                }
            } else if (toLowerCase.startsWith("bit") || toLowerCase.startsWith("boolean")) {
                return "boolean";
            } else if (toLowerCase.startsWith("blob")
                || toLowerCase.startsWith("binary")
                || toLowerCase.startsWith("varcharbinary")
                || toLowerCase.startsWith("longvarcharbinary")) {
                return "bytea";
            } else if (toLowerCase.startsWith("varchar")) {
                if (Objects.nonNull(columnSize)) {
                    return "varchar(" + columnSize + ")";
                }
                return "varchar(500)";
            } else if (toLowerCase.startsWith("char")) {
                if (Objects.nonNull(columnSize)) {
                    return "char(" + columnSize + ")";
                }
                return "char(500)";
            } else {
                return "text";
            }
        }
        return toPostgresqlType(sqlType, columnSize, digitSize);
    }

    public String toPostgresqlType(int sqlType, String fieldType) {
        if (sqlType == 0) {
            String toLowerCase = fieldType.toLowerCase(Locale.ROOT);
            // inter int
            if (toLowerCase.startsWith("in") || toLowerCase.startsWith("bigint")) {
                return "int4";
            } else if (toLowerCase.startsWith("time")) {
                return "timestamp(0)";
            } else if (toLowerCase.startsWith("date")) {
                return "date";
            } else if (toLowerCase.startsWith("float")
                || toLowerCase.startsWith("numeric")
                || toLowerCase.startsWith("decimal")
                || toLowerCase.startsWith("double")) {
                return "float8";
            } else if (toLowerCase.startsWith("bit") || toLowerCase.startsWith("boolean")) {
                return "boolean";
            } else if (toLowerCase.startsWith("blob")
                || toLowerCase.startsWith("binary")
                || toLowerCase.startsWith("varcharbinary")
                || toLowerCase.startsWith("longvarcharbinary")) {
                return "bytea";
            } else {
                return "text";
            }
        }
        return toPostgresqlType(sqlType);
    }

    public String toPostgresqlType(int sqlType, Integer columnSize, Integer digitSize) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "int4";
            case Types.VARCHAR:
                if (Objects.nonNull(columnSize)) {
                    return "varchar(" + columnSize + ")";
                }
                return "varchar(500)";
            case Types.CHAR:
                if (Objects.nonNull(columnSize)) {
                    return "char(" + columnSize + ")";
                }
                return "char(500)";
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "text";
            case Types.DATE:
                return "date";
            case Types.TIME:
                return "time";
            case Types.TIMESTAMP:
                return "timestamp(0)";
            case Types.FLOAT:
            case Types.REAL:
                return "float4";
            case Types.DOUBLE:
                return "float8";
            case Types.NUMERIC:
            case Types.DECIMAL:
                if (Objects.nonNull(columnSize)
                    && Objects.nonNull(digitSize)
                    && columnSize > 0
                    && digitSize >= 0) {
                    return "numeric(" + columnSize + "," + digitSize + ")";
                } else {
                    return "numeric";
                }
            case Types.BIT:
            case Types.BOOLEAN:
                return "boolean";
            case Types.TINYINT:
                return "int2";
            case Types.BIGINT:
                return "int8";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "bytea";
            default:
                return "text";
        }
    }

    public String toPostgresqlType(int sqlType) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "int4";
            case Types.VARCHAR:
                return "varchar(500)";
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "text";
            case Types.DATE:
                return "date";
            case Types.TIME:
            case Types.TIMESTAMP:
                return "timestamp(0)";
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                return "float8";
            case Types.BIT:
            case Types.BOOLEAN:
                return "boolean";
            case Types.TINYINT:
                return "int2";
            case Types.BIGINT:
                return "int8";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "bytea";
            default:
                return "text";
        }
    }

    public String toMysqlType(int sqlType) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "int";
            case Types.VARCHAR:
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "text";
            case Types.DATE:
                return "date";
            case Types.TIME:
                return "time";
            case Types.TIMESTAMP:
                return "datetime";
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                return "double";
            case Types.BIT:
            case Types.BOOLEAN:
                return "boolean";
            case Types.TINYINT:
                return "tinyint";
            case Types.BIGINT:
                return "bigint";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "blob";
            default:
                return "text";
        }
    }

    public String toTiDBType(int sqlType, String fieldType) {
        if (sqlType == 0) {
            String lowerCase = fieldType.toLowerCase(Locale.ROOT);
            if (lowerCase.startsWith("in")) {
                return "int";
            } else if (lowerCase.startsWith("time")) {
                return "time";
            } else if (lowerCase.startsWith("timestamp")) {
                return "datatime";
            } else if (lowerCase.startsWith("date")) {
                return "date";
            } else if (lowerCase.startsWith("float")
                || lowerCase.startsWith("numeric")
                || lowerCase.startsWith("decimal")
                || lowerCase.startsWith("double")) {
                return "double";
            } else if (lowerCase.startsWith("bit") || lowerCase.startsWith("boolean")) {
                return "boolean";
            } else if (lowerCase.startsWith("bigint")) {
                return "bigint";
            } else if (lowerCase.startsWith("blob")
                || lowerCase.startsWith("binary")
                || lowerCase.startsWith("varcharbinary")
                || lowerCase.startsWith("longvarcharbinary")) {
                return "blob";
            } else {
                return "text";
            }
        }
        return toTiDBType(sqlType);
    }

    public String toTiDBType(int sqlType) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "int";
            case Types.VARCHAR:
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "text";
            case Types.DATE:
                return "date";
            case Types.TIME:
                return "time";
            case Types.TIMESTAMP:
                return "datetime";
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                return "double";
            case Types.BIT:
            case Types.BOOLEAN:
                return "boolean";
            case Types.TINYINT:
                return "tinyint";
            case Types.BIGINT:
                return "bigint";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "blob";
            default:
                return "text";
        }
    }

    public String toOracleType(int sqlType) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "int";
            case Types.VARCHAR:
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "varchar2";
            case Types.DATE:
                return "date";
            case Types.TIME:
            case Types.TIMESTAMP:
                return "timestamp";
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                return "number";
            case Types.BIT:
            case Types.BOOLEAN:
                return "boolean";
            case Types.TINYINT:
                return "smallint";
            case Types.BIGINT:
                return "long";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "blob";
            default:
                return "varchar2";
        }
    }

    public String toDb2Type(int sqlType) {
        throw new UnsupportedOperationException("暂不支持");
    }

    public String toSqlserverType(int sqlType) {
        throw new UnsupportedOperationException("暂不支持");
    }

    //  public String toDataApiType(String fieldType){
    //
    //
    //  }

    public String toSapType(int sqlType, String fieldType) {
        if (sqlType == 0) {
            String lowerCase = fieldType.toLowerCase(Locale.ROOT);
            if (lowerCase.startsWith("in")) {
                return "integer";
            } else if (lowerCase.startsWith("time")) {
                return "timestamp";
            } else if (lowerCase.startsWith("date")) {
                return "date";
            } else if (lowerCase.startsWith("float")
                || lowerCase.startsWith("numeric")
                || lowerCase.startsWith("decimal")
                || lowerCase.startsWith("double")) {
                return "double";
            } else if (lowerCase.startsWith("bit") || lowerCase.startsWith("boolean")) {
                return "boolean";
            } else if (lowerCase.startsWith("bigint")) {
                return "bigint";
            } else if (lowerCase.startsWith("blob")
                || lowerCase.startsWith("binary")
                || lowerCase.startsWith("varcharbinary")
                || lowerCase.startsWith("longvarcharbinary")) {
                return "varbinary";
            } else {
                return "text";
            }
        }
        return toSapType(sqlType);
    }

    public String toSapType(int sqlType) {
        switch (sqlType) {
            case Types.INTEGER:
            case Types.SMALLINT:
                return "integer";
            case Types.VARCHAR:
                return "varchar(500)";
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "text";
            case Types.DATE:
                return "date";
            case Types.TIME:
            case Types.TIMESTAMP:
                return "timestamp";
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.DOUBLE:
            case Types.REAL:
                return "double";
            case Types.BIT:
            case Types.BOOLEAN:
                return "boolean";
            case Types.TINYINT:
                return "tinyint";
            case Types.BIGINT:
                return "bigint";
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                return "varbinary";
            default:
                return "text";
        }
    }

    public String toDataApiType(String fieldType) {
        switch (fieldType) {
            // int
            case "int":
            case "integer":
                return "INT";
            // double
            case "double":
                return "DOUBLE";
            case "float":
                return "FLOAT";
        }

        return null;
    }

    protected String toReadableMsg(SQLException e) {
        String connectionErrMsg = "连接失败，请检查数据库服务是否可用或数据库链接是否正确";
        Throwable t = getThrowable(e);
        if (t instanceof SocketTimeoutException || t instanceof ConnectException) {
            return connectionErrMsg;
        }
        return "连接失败，请检查用户名、密码、数据库名等参数是否正确";
    }

    private Throwable getThrowable(Throwable t) {
        while (true) {
            Throwable cause = t.getCause();
            if (cause == null) {
                return t;
            }
            t = t.getCause();
        }
    }
}
